CM4125 Week 7: Time Series Data + Running Bar Charts¶
Lecture objectives¶
1) Understand the concept of time series data and what does it imply
2) Process time series datasets into effective dataframes
3) Produce running bar charts using a variety of tools
What is Time Series Data (TSD)?¶
Simply put, datasets that describe/contain data which varies over time
They are usually characterised by a fixed and constant aggregation of data
Readings from sensors in the industry
Coronavirus cases
Trends in economicsThe art of using these datasets resides in knowing how to shape them and using the right features
To practice, we will use the dataset called bitcoin.csv
This data set contains the price of the Bitcoin cryptocurrency on the US-based exchange Gemini, minute-by-minute for all of 2017
import pandas as pd
import numpy as np
bitcoin = pd.read_csv('https://www.dropbox.com/s/kxjc92oia6gcoy8/bitcoin.csv?raw=1', index_col=0)
bitcoin
Note that the Date column contains entries formatted as a date-time such as 2017-01-01 00:00:00
First, let's check the type of this data by examining the first item
first_index_value = bitcoin.index[0]
print(first_index_value)
print(type(first_index_value))
We can avoid this in one go by parsing the dates when loading
bitcoin = pd.read_csv('https://www.dropbox.com/s/kxjc92oia6gcoy8/bitcoin.csv?raw=1',
parse_dates=['Date'], index_col=0)
bitcoin
If we check the column now, it is marked as Timestamp
first_index_value = bitcoin.index[0]
print(first_index_value)
print(type(first_index_value))
Exploring TSD¶
This data set contains the following columns:
Price Data:
- Open - The price of the commodity (bitcoin) at the start of the time period (minute)
- High - The highest value the price reached during the time period
- Low - The lowest value the price reached during the time period
- Close - The price at the end of the time period
Trade Data:
- Volume - The total amount of the commodity bought/sold in the time period
Other time series data sets with have other columns, but this is common for financial data.
Even though the index is stored as Timestamp, we can retrieve rows by querying with a string
For example during the minute 2017-05-27 19:30:00, we can query using 2017-05-27 19:30:00 which matches the data
However, since the data set has only minute-by-minute data, the seconds don't matter, so we can exclude the seconds in our query and search for 2017-05-27 19:30
bitcoin.loc['2017-05-27 19:30']
The price started this minute at 2144.68 USD, dropped to 2142.53 USD at the end of the minute
During this time the minimum value was 2141.26 USD and the maximum value was 2144.68 USD
0.05406 bitcoin was bought/sold during this minute
Let's check the next minute 2017-05-27 19:31
bitcoin.loc['2017-05-27 19:31']
The previous minute closed at 2142.53 USD and this minute opened at 2142.53 USD
This is expected since the end of one minute is the same as the start of the next minute!
If we want to check the open price for the day and close price for the day, we could check the open price for the first minute and close price for the last minute
print('Date: 2017-05-27')
print('Open:', bitcoin.at['2017-05-27 00:00', 'Open'])
print('Close:', bitcoin.at['2017-05-27 23:59', 'Close'])
We can select an interval of time the same way we do with numbers
With the following code, we select all of the values in May of 2017
bitcoin[(bitcoin.index >= '2017-05') & (bitcoin.index < '2017-06')]
Selecting all of the values for 1st May before noon
bitcoin[(bitcoin.index >= '2017-05-01') & (bitcoin.index < '2017-05-01 12:00')]
Calculations with TSD¶
We may want to calculate other values such as taking the middle value between the Low and High values, this will allow us to calculate an estimate for the value of the bitcoin traded
It will only be an estimate. since we don't have the exact list of trades available in this dataset!
We can perform calculations on the columns in exactly the same we we have with any other data set before
bitcoin['Middle'] = (bitcoin['High'] + bitcoin['Low']) / 2
bitcoin['USD Volume'] = bitcoin['Middle'] * bitcoin['Volume']
bitcoin
Resampling TSD¶
Sometimes we are interested in sampling the in larger chunks than it is presented
For instance, this data has one line per minute; we may want data for days instead
The method to resample the data in Python is called resample, and takes a time period as an argument
For example, 1D means 1 day (you can see more options here)
# Not very useful output right?
bitcoin.resample('1D')
If we call the mean method, it will average out the values of this new object by day
bitcoin.resample('1D').mean()
The method takes the values in the column Volume and rows 2017-01-01 00:00 to 2017-01-01 23:59 and averaging them
We can also get the total by adding the values
bitcoin.resample('1D').sum()
This operation was more useful for the Volume column, as it now sums up all of the entries for that day
We can see that 1070.013119 bitcoin (about $1,061,594 at the time) were traded on 01/01
In fact there was another way to get this!
Multipying the bitcoin per minute rate from before (0.743065) times the number of minute in a day (1440)1440*0.743065
Resampling TSD by Column¶
If we want the lowest or highest price for the whole day, we don't need an average or a sum!
We can tell Pandas to use max on these values, and also we can use min for Low
bitcoin.resample('1D').max()
bitcoin.resample('1D').min()
To generate a new dataframe with both changes applied to the respective column in one line of code, we will use the resample, lambda and apply methods:
For
Highwe willmaxof the values ofHighfor each minute.For
Lowwe will usemin.For
VolumeandUSD Volumewe will usesum.For
Openwe will use the first item (index[0]).For
Closewe will use the last item (index[-1]).
daily = bitcoin.resample('1D').apply({'Open' : lambda x: x[0],
'High' : np.max,
'Low' : np.min,
'Close' : lambda x: x[-1],
'Volume' : np.sum,
'USD Volume' : np.sum})
daily
Notice that the Close of one day still matches the Open of the next
Moreover, we have deliberately left out Middle since we want to recalculate it on a daily basis, as it will be different from what we calculate on a minute basis!
We will leave the USD Volume based on minute data since that will give a more accurate estimate and this column is not exact since we don't have the trades list
Let's recalculate Middle on this new daily data
daily['Middle'] = (daily['High'] + daily['Low']) / 2
daily
Plotting TSD¶
Typically, the first plot used for this type of data is a line plot
import plotly.express as px
fig = px.line(daily.reset_index(), x='Date', y='Middle')
fig.show()
However, if we had the data of one more cryptocurrency (yes, there's more than bitcoin!), we could compare their trends and see which one is trading higher!
And that is why people came up with... RUNNING BAR CHARTS (RBC)!
Let's see how this example was produced using Python
We will use a file called city_populations.txt which you can download from here
This file contains population data of different world cities through time
df = pd.read_csv('https://gist.githubusercontent.com/johnburnmurdoch/4199dbe55095c3e13de8d5b2e5e5307a/raw/fa018b25c24b7b5f47fd0568937ff6c04e384786/city_populations')
df
Let's clean this a little bit...
# These are the only columns I need
df = df[['name','group','year','value']]
df
We will change the names of the columns to something more coherent!
df = df.rename(columns={"name": "City", "group": "Continent","year": "Year", "value":"Population"})
df
Notice that in the Continent column we have India for some cities
If I'm correct, India is a sub-continent rather than a continent!
Let's see the continents listed here...
set(df['Continent'])
Middle East and Latin America are also listed as continents!
# Cities in the "Indian continent"
print(set(df[df['Continent']=='India']['City']))
# Cities with "Middle East" continent
print(set(df[df['Continent']=='Middle East']['City']))
# Cities with "Middle East" continent
print(set(df[df['Continent']=='Latin America']['City']))
Let's first replace India -> Asia
df['Continent'] = df['Continent'].replace('India', 'Asia')
With the other two is slightly different, as these categories contain cities from different continents!
In fact I may need to change "one by one"
df['Continent'][df['City']=='Fez']='Africa'
df['Continent'][df['City']=='Cairo']='Africa'
df['Continent'] = df['Continent'].replace('Middle East', 'Asia')
df['Continent'][df['City']=='Mexico City']='North America'
df['Continent'] = df['Continent'].replace('Latin America', 'South America')
df
It shows a warning, but it works!
# Show that the relacement has been successful
df[df['City']=='Mexico City']
I will save the new dataset locally
df.to_csv(r'city_populations_updated.csv',index=False)
Now, let's try to do our own RBC with Python and the matplotlib package
# Importing the necessary packages
import matplotlib.pyplot as plt
import matplotlib.ticker as ticker
If we just plot city vs population using ax.barh, we will see the last value of population for each of the cities
fig, ax = plt.subplots(figsize=(15, 8))
ax.barh(df['City'], df['Population'])
To do a running bar chart, we need to define a given year and sort the cities from most to least populated (for that year)
current_year = 1500
df2 = (df[df['Year'].eq(current_year)]
.sort_values(by='Population',ascending=False))
df2
Notice that in this dataset, we don't have all cities for each year so we would get different charts
# Plotting a basic bar chart
fig, ax = plt.subplots(figsize=(15, 8))
ax.barh(df2['City'], df2['Population'])
To add colours and labels (for each continent), we will use a dictionary that pairs cities with their respective continent
colours = dict(zip(
['Europe', 'Asia', 'South America', 'North America', 'Africa'],
['#adb0ff', '#90d595','#aafbff', '#f7bb5f', '#eafb50']))
group_lk = df.set_index('City')['Continent'].to_dict()
group_lk
Now we can use this pairing to get some colours in df2
fig, ax = plt.subplots(figsize=(15, 8))
df2 = df2[::-1] # flip values from top to bottom
# pass colours values to `color=`
ax.barh(df2['City'], df2['Population'], color=[colours[group_lk[x]] for x in df2['City']])
# iterate over the values to plot labels and values (Tokyo, Asia, 38194.2)
for i, (value, name) in enumerate(zip(df2['Population'], df2['City'])):
ax.text(value, i, name, ha='right')
ax.text(value, i-.25, group_lk[name], ha='right')
ax.text(value, i, value, ha='left')
# Add year right middle portion of canvas
ax.text(1, 0.4, current_year, transform=ax.transAxes, size=46, ha='right')
This function will draw a more "stylish" function to create the last chart
# Frankly, I don't know what half of it does...
def draw_barchart(year):
dff = df[df['Year'].eq(year)].sort_values(by='Population', ascending=True).tail(10)
ax.clear()
ax.barh(dff['City'], dff['Population'], color=[colours[group_lk[x]] for x in dff['City']])
dx = dff['Population'].max() / 200
for i, (value, name) in enumerate(zip(dff['Population'], dff['City'])):
ax.text(value-dx, i, name, size=14, weight=600, ha='right', va='bottom')
ax.text(value-dx, i-.25, group_lk[name], size=10, color='#444444', ha='right', va='baseline')
ax.text(value+dx, i, f'{value:,.0f}', size=14, ha='left', va='center')
# ... polished styles
ax.text(1, 0.4, year, transform=ax.transAxes, color='#777777', size=46, ha='right', weight=800)
ax.text(0, 1.06, 'Population (thousands)', transform=ax.transAxes, size=12, color='#777777')
ax.xaxis.set_major_formatter(ticker.StrMethodFormatter('{x:,.0f}'))
ax.xaxis.set_ticks_position('top')
ax.tick_params(axis='x', colors='#777777', labelsize=12)
ax.set_yticks([])
ax.margins(0, 0.01)
ax.grid(which='major', axis='x', linestyle='-')
ax.set_axisbelow(True)
ax.text(0, 1.12, 'The most populous cities in the world ',
transform=ax.transAxes, size=24, weight=600, ha='left')
plt.box(False)
# Now we execute the function
fig, ax = plt.subplots(figsize=(15, 8))
draw_barchart(1988)
The most important part is to animate the plot for a range of years
import matplotlib.animation as animation
from IPython.display import HTML
fig, ax = plt.subplots(figsize=(15, 8))
animator = animation.FuncAnimation(fig, draw_barchart, frames=range(2000,2020))
HTML(animator.to_jshtml())
Any easier way to do this?¶
Besides the large amount of code, one of the biggest letdown of this is the lack of animation for the bars to "pass" each other!
Therefore,...
Lab¶
There are two parts, one for TSD and another for RBC
The first one consists on following the instructions to treat TSD data
The second one has the code to do the RBC
For that one, I want you to get creative!
- Can you change colours, styles, names, etc.?
- Can you skip some years?
- Can you get rid of the static plot that gets printed after the animation?
- Can you show not only the top 10 cities?
- Can you show all cities at the same time?
- Can you put the years of the range in the title of the chart?
- Can you run it from 1500 to 2020?
- Can you make the chart go back in time?
Alternatively you can download the dataset from Moodle and see if other tools are able to these things!